package com.todod.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReaderXlsx {
	private XSSFWorkbook wb = null; // book [includes sheet]

	private XSSFSheet sheet = null;

	private Row row = null;

	private int sheetNum = 0; // 第sheetnum个工作表
	private int rowNum = 0;

	private InputStream fis = null;

	private File file = null;

	private java.text.DecimalFormat formatter = new java.text.DecimalFormat("##################");

	public ExcelReaderXlsx() {
	}

	public ExcelReaderXlsx(File file) {
		this.file = file;
	}

	public ExcelReaderXlsx(InputStream fis) {
		this.fis = fis;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	// 根据sheetName得到sheetNum
	public int getSheetNumBySheetName(String sheetName) {

		return wb.getSheetIndex(sheetName);
	}

	public String getSheetNameBySheetNum(int sheetNum) {

		return wb.getSheetName(sheetNum);
	}

	/**
	 *
	 * @Title: getSheetName
	 * @Description: 获取sheet页名称
	 * @author：gsh
	 * @date: 2019年4月3日
	 * @return
	 * @return String
	 */
	public String getSheetName() {

		return wb.getSheetName(sheetNum);
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 读取excel文件获得SXSSFWorkbook对象
	 */
	public void open() throws IOException {
		if (fis == null)
			fis = new FileInputStream(file);
		wb = new XSSFWorkbook((fis));
		fis.close();
	}

	/**
	 * 返回sheet表数目
	 *
	 * @return int
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 *
	 * @Title: getColoumCount
	 * @Description: 总列数，遍历从0开始 <countNum
	 * @author：gsh
	 * @date: 2019年4月4日
	 * @return
	 * @return int
	 */
	public int getColoumCount() {
		if (wb == null) {
			System.out.println("=============>WorkBook为空");
		}
		XSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int coloumNum = sheet.getRow(0).getLastCellNum();
		return coloumNum;
	}

	/**
	 * sheetNum下的记录行数 ：实际值为总行数-1
	 *
	 * @return int
	 */
	public int getRowCount() {
		if (wb == null) {
			System.out.println("=============>WorkBook为空");
		}
		XSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 读取指定sheetNum的rowCount
	 *
	 * @param sheetNum
	 * @return int
	 */
	public int getRowCount(int sheetNum) {
		XSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 得到指定行的内容
	 *
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行数的内容
	 *
	 * @param sheetNum
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0) {
			return null;
		}
		String[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);

			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount + 1];
			for (int i = 0; i <= cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 读取指定列的内容
	 *
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 *
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的内容
	 *
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0) {
			return "";
		}
		String strExcelCell = "", tempString = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell(cellNum) != null) { // add this condition
				// judge
				switch (row.getCell(cellNum).getCellType()) {
					case FORMULA:
						try {
							strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());
						} catch (IllegalStateException e) {
							strExcelCell = String.valueOf(row.getCell(cellNum).getRichStringCellValue());
						}
						break;
					case NUMERIC: {
						tempString = "" + row.getCell(cellNum).getNumericCellValue();
						if (tempString != null && tempString.indexOf(".") != -1) {
							strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());
						} else {

							strExcelCell = formatter.format(row.getCell(cellNum).getNumericCellValue());

						}
					}
					break;
					case STRING:
						strExcelCell = row.getCell(cellNum).getStringCellValue();
						break;
					case BLANK:
						strExcelCell = "";
						break;
					default:
						strExcelCell = "";
						break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	// 读取数字的时候不会包含小数点
	public String readStringExcelCell2(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0) {
			return "";
		}
		String strExcelCell = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell(cellNum) != null) { // add this condition
				// judge
				switch (row.getCell(cellNum).getCellType()) {
					case FORMULA:
						try {
							strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());
						} catch (IllegalStateException e) {
							strExcelCell = String.valueOf(row.getCell(cellNum).getRichStringCellValue());
						}
						break;
					case NUMERIC: {
						strExcelCell = formatter.format(row.getCell(cellNum).getNumericCellValue());
					}
					break;
					case STRING:
						strExcelCell = row.getCell(cellNum).getStringCellValue();
						break;
					case BLANK:
						strExcelCell = "";
						break;
					default:
						strExcelCell = "";
						break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	public static void main(String args[]) {
		File file = new File("C:\\Staff.xls");
		ExcelReaderXlsx readExcel = new ExcelReaderXlsx(file);
		try {
			readExcel.open();
		} catch (IOException e) {
			e.printStackTrace();
		}
		// String sheetName = readExcel.getSheetNameBySheetNum(2);
		int sheetNum = readExcel.getSheetNumBySheetName("我的");
		System.out.println("sheetNum==" + sheetNum);
		// readExcel.setSheetNum(0); // 设置读取索引为0的工作表
		// // 总行数
		// int count = readExcel.getRowCount();
		// int columCount = 42;
		//
		// String sql = "", name = "";
		// StringBuffer sqlBufferKey = new StringBuffer();
		// StringBuffer sqlBufferValue = new StringBuffer();
		//
		// final String COMMA = ",", SINGLECOMMA = "'";
		//
		// //i为行数，j为列数
		// for (int i = 1; i <= count; i++) {
		//
		// for (int j = 0; j < columCount; j++) {
		// name = readExcel.readStringExcelCell(0, j);
		// sqlBufferKey.append(name);
		// if (!name.equalsIgnoreCase("price") &&
		// !name.equalsIgnoreCase("bookSumNum") &&
		// !name.equalsIgnoreCase("borrowedNum") &&
		// !name.equalsIgnoreCase("borrowedCount")) {
		// sqlBufferValue.append(SINGLECOMMA +
		// readExcel.readStringExcelCell(i, j) +
		// SINGLECOMMA);
		// } else {
		// sqlBufferValue.append(readExcel.readStringExcelCell(i, j));
		// }
		//
		// if (j != columCount - 1) {
		// sqlBufferKey.append(COMMA);
		// sqlBufferValue.append(COMMA);
		// }
		//
		// }
		// sql = "insert into book (" + sqlBufferKey.toString() + ") values (" +
		// sqlBufferValue.toString() + ")";
		// sqlBufferKey = new StringBuffer();
		// sqlBufferValue = new StringBuffer();
		// System.out.println("i==" + i + ":::sql==" + sql);
		// }

	}

	public String readStringExcelCellWithXiaoShu(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0) {
			return "";
		}
		String strExcelCell = "", tempString = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell(cellNum) != null) { // add this condition
				// judge
				switch (row.getCell(cellNum).getCellType()) {
					case FORMULA:
						try {
							strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());
						} catch (IllegalStateException e) {
							strExcelCell = String.valueOf(row.getCell(cellNum).getRichStringCellValue());
						}
						break;
					case NUMERIC: {
						tempString = "" + row.getCell(cellNum).getNumericCellValue();
						if (tempString != null && tempString.indexOf(".") != -1) {
							strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());
						} else {

							strExcelCell = formatter.format(row.getCell(cellNum).getNumericCellValue());

						}
					}
					break;
					case STRING:
						strExcelCell = row.getCell(cellNum).getStringCellValue();
						break;
					case BLANK:
						strExcelCell = "";
						break;
					default:
						strExcelCell = "";
						break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}
}
